The following expression works for GNU sed:
sed -E 's/^"name"[[:space:]]*"([^"]+)"$/\1/'
This works by creating an regular expression that matches the entire line as well as matching a subexpression where the name portion desired is found. This is the section in the parenthesis, which a sequence of characters that are not a double quote. The entire matched regex is replaced by the subexpression, referenced as
\1 at the end of the expression.
As an example of how this works:
$ # Generate lines in the expected format
$ echo $'"name" "Andrew Spokes"\n"name" "ABC"\n"name" "Foo Bar Baz"'
"name" "Andrew Spokes"
"name" "Foo Bar Baz"
$ # Pipe the same lines to the sed command
$ echo $'"name" "Andrew Spokes"\n"name" "ABC"\n"name" "Foo Bar Baz"' | sed -E 's/^"name"[[:space:]]*"([^"]+)"$/\1/'
Foo Bar Baz
If your experience with GNU regular expressions is limited it may help to look up the constructs used in the above regex in the GNU sed manual. If using a different flavor of sed the details might be slightly different. One callout is the
-E option, this enables "extended" regular expressions. In GNU sed this has special characters like parens and backslashes take on the meaning of creating subexpressions and back references without escaping them. Another is the use of
$ to require the full regex to match at the start and end of the line.
The other callout is simply that the sed expression used makes assumptions about how uniform all the input lines are. Adjustments will be needed if there is more variation in the input.